use mysql::Value;
use crate::rules::Vo;
use crate::util::{db, cap};

// 查询交路排班数量
pub fn route_count(vo: &Vo) -> u32 {
    let len = vo.len();
    let mut paras = Vec::with_capacity(len);
    let mut sql = String::with_capacity(cap(len));
    sql.push_str("select count(1) from route");
    if len > 0 {
        sql.push_str(" where 1=1");
        if vo.not_blank("name") {
            sql.push_str(" and name like ?");
            paras.push(Value::from(format!("{}%", vo.str("name"))));
        }
        if vo.not_blank("trains") {
            sql.push_str(" and trains=?");
            paras.push(Value::from(vo.str("trains")));
        }
        if vo.not_blank("workDate") {
            sql.push_str(" and workDate=?");
            paras.push(Value::from(vo.str("workDate")));
        }
        if vo.not_blank("quitDate") {
            sql.push_str(" and quitDate=?");
            paras.push(Value::from(vo.str("quitDate")));
        }
    }
    
    db::count(&sql, &paras)
}

// 查询交路排班列表
pub fn route_list(cols: &str, vo: &Vo) -> Vec<Vo> {
    let len = vo.len();
    let mut paras = Vec::with_capacity(len);
    let mut sql = String::with_capacity(cap(len));
    sql.push_str(&format!("select {} from route", cols));
    if len > 0 {
        sql.push_str(" where 1=1");
        if vo.not_blank("name") {
            sql.push_str(" and name like ?");
            paras.push(Value::from(format!("{}%", vo.str("name"))));
        }
        if vo.not_blank("trains") {
            sql.push_str(" and trains=?");
            paras.push(Value::from(vo.str("trains")));
        }
        if vo.not_blank("workDate") {
            sql.push_str(" and workDate=?");
            paras.push(Value::from(vo.str("workDate")));
        }
        if vo.not_blank("startDate") {
            sql.push_str(" and workDate>=?");
            paras.push(Value::from(vo.str("startDate")));
        }
        if vo.not_blank("endDate") {
            sql.push_str(" and workDate<=?");
            paras.push(Value::from(vo.str("endDate")));
        }
        if vo.not_blank("quitDate") {
            sql.push_str(" and quitDate=?");
            paras.push(Value::from(vo.str("quitDate")));
        }
        if vo.not_blank("groupBy") {
            sql.push_str(" group by ");
            sql.push_str(vo.str("groupBy"));
        }
        if vo.not_blank("page") {
            let size = vo.int("size");
            sql.push_str(" limit ");
            sql.push_str(&format!("{}", (vo.int("page") - 1) * size));
            sql.push(',');
            sql.push_str(&format!("{}", size));
        }
    }
    
    db::list(&sql, &paras)
}

// 根据ID查询交路排班信息
pub fn get_route(cols: &str, id: u32) -> Vo {
    let sql = format!("select {} from route where id=? limit 1", cols);
    let paras = vec![Value::from(id)];
    
    db::get(&sql, &paras)
}

// 保存交路排班信息
pub fn save_route(list: Vec<Vo>) {
    let vo = &list[0];
    if list.len() == 1 {
        let map = map!{
            "id" => Value::from(vo.long("id")),
            "userId" => Value::from(vo.long("userId")),
            "name" => Value::from(vo.str("name")),
            "trains" => Value::from(vo.str("trains")),
            "days" => Value::from(vo.double("days")),
            "workDate" => Value::from(vo.str("workDate")),
            "quitDate" => Value::from(vo.str("quitDate"))
        };
        let _ = db::update("route", &map);
    } else {
        let before_sql="delete from route where workDate=?";
        let after_sql="insert into route(userId,name,trains,days,workDate,quitDate) values (?,?,?,?,?,?)";
        let mut paras = Vec::with_capacity(list.len());
        for vo in list.iter() {
            if vo.blank("userId") {
                paras.push(vec![Value::NULL, Value::from(""), Value::from(vo.str("trains")), Value::from(vo.double("days")), Value::from(vo.str("workDate")), Value::from(vo.str("quitDate"))]);                
            } else {
                paras.push(vec![Value::from(vo.long("userId")), Value::from(vo.str("name")), Value::from(vo.str("trains")), Value::from(vo.double("days")), Value::from(vo.str("workDate")), Value::from(vo.str("quitDate"))]);
            }
        }
        
        let _=db::batch2sql(before_sql, &[Value::from(vo.str("workDate"))], after_sql, &paras);
    }
}
